-- ================================================================
-- SQLCheck 兼容性规则初始化脚本
-- 版本: v4.2
-- 创建日期: 2025-09-04
-- 基于: @docs/Rule/Oracle2GoldenDB.md 和 @docs/Rule/Mysql2GoldenDB.md
-- 描述: 完整的兼容性规则集合，包含48个规则
-- ================================================================

-- 清空现有所有规则（完全重建）
DELETE FROM compatibility_rules;

-- ========================================
-- Oracle到GoldenDB兼容性规则
-- ========================================

-- 重置AUTO_INCREMENT
ALTER TABLE compatibility_rules AUTO_INCREMENT = 1;

-- ================================================================
-- Oracle到GoldenDB兼容性规则集合（44个规则）
-- ================================================================
INSERT INTO compatibility_rules (rule_id, rule_name, category, source_db_type, target_db_type, rule_pattern, severity, description, suggestion, example_sql, suggested_sql, is_active) VALUES

-- 数据类型兼容性规则（16个）
('OR_CHAR_001', 'Oracle CHAR长度限制', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 'CHAR\\s*\\(\\s*([2-9][0-9]{2,}|[1-9][0-9]{3,})\\s*\\)', 'HIGH', 
 'Oracle CHAR最大长度为2000，GoldenDB CHAR最大长度为255。当CHAR长度超过255时会出现兼容性问题。', 
 '建议采用VARCHAR替换超过255长度的CHAR字段。', 
 'CREATE TABLE users (name CHAR(500), code CHAR(1000));', 
 'CREATE TABLE users (name VARCHAR(500), code VARCHAR(1000));', 
 TRUE),

('OR_CHAR_002', 'Oracle NVARCHAR2类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bNVARCHAR2\\b', 'HIGH', 
 'GoldenDB不支持NVARCHAR2类型', 
 '建议采用VARCHAR替换', 
 'CREATE TABLE products (name NVARCHAR2(100), description NVARCHAR2(500));', 
 'CREATE TABLE products (name VARCHAR(100), description VARCHAR(500));', 
 TRUE),

('OR_NUM_001', 'Oracle NUMBER类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bNUMBER\\b(?!\\s*\\()', 'HIGH', 
 'GoldenDB不支持NUMBER类型（不带精度）', 
 '建议采用DECIMAL替换，指定合适的精度和标度', 
 'CREATE TABLE accounts (balance NUMBER, rate NUMBER);', 
 'CREATE TABLE accounts (balance DECIMAL(10,2), rate DECIMAL(5,2));', 
 TRUE),

('OR_NUM_002', 'Oracle BINARY_FLOAT类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bBINARY_FLOAT\\b', 'HIGH', 
 'GoldenDB不支持BINARY_FLOAT类型', 
 '建议采用FLOAT替换', 
 'CREATE TABLE measurements (temperature BINARY_FLOAT, pressure BINARY_FLOAT);', 
 'CREATE TABLE measurements (temperature FLOAT, pressure FLOAT);', 
 TRUE),

('OR_NUM_003', 'Oracle BINARY_DOUBLE类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bBINARY_DOUBLE\\b', 'HIGH', 
 'GoldenDB不支持BINARY_DOUBLE类型', 
 '建议采用DOUBLE或DECIMAL替换', 
 'CREATE TABLE scientific_data (value BINARY_DOUBLE, coefficient BINARY_DOUBLE);', 
 'CREATE TABLE scientific_data (value DOUBLE, coefficient DOUBLE);', 
 TRUE),

('OR_DATE_001', 'Oracle DATE精度问题', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bDATE\\b', 'MEDIUM', 
 'Oracle的DATE精度精确到秒，而GoldenDB的DATE精度精确到天', 
 '建议采用DATETIME替换以保持精度', 
 'CREATE TABLE events (created_date DATE, modified_date DATE);', 
 'CREATE TABLE events (created_date DATETIME, modified_date DATETIME);', 
 TRUE),

('OR_DATE_002', 'Oracle TIMESTAMP精度限制', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 'TIMESTAMP\\s*\\(\\s*([789])\\s*\\)', 'LOW', 
 'Oracle TIMESTAMP支持最大9位小数秒，GoldenDB最大支持6位', 
 '调整TIMESTAMP精度不超过6位', 
 'CREATE TABLE logs (event_time TIMESTAMP(9), process_time TIMESTAMP(8));', 
 'CREATE TABLE logs (event_time TIMESTAMP(6), process_time TIMESTAMP(6));', 
 TRUE),

('OR_DATE_003', 'Oracle TIMESTAMP WITH TIME ZONE', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bTIMESTAMP\\s+WITH\\s+TIME\\s+ZONE\\b', 'HIGH', 
 'GoldenDB不支持指定时区的TIMESTAMP', 
 '使用TIMESTAMP类型并在应用层处理时区', 
 'CREATE TABLE global_events (event_time TIMESTAMP WITH TIME ZONE, created_at TIMESTAMP WITH TIME ZONE);', 
 'CREATE TABLE global_events (event_time TIMESTAMP, created_at TIMESTAMP);', 
 TRUE),

('OR_DATE_004', 'Oracle TIMESTAMP WITH LOCAL TIME ZONE', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bTIMESTAMP\\s+WITH\\s+LOCAL\\s+TIME\\s+ZONE\\b', 'HIGH', 
 'GoldenDB不支持本地时区的TIMESTAMP', 
 '使用TIMESTAMP类型并在应用层处理时区转换', 
 'CREATE TABLE user_activities (login_time TIMESTAMP WITH LOCAL TIME ZONE, logout_time TIMESTAMP WITH LOCAL TIME ZONE);', 
 'CREATE TABLE user_activities (login_time TIMESTAMP, logout_time TIMESTAMP);', 
 TRUE),

('OR_INTERVAL_001', 'Oracle INTERVAL DAY TO SECOND', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bINTERVAL\\s+DAY\\s+TO\\s+SECOND\\b', 'HIGH', 
 'GoldenDB不支持INTERVAL DAY TO SECOND类型', 
 '使用数值类型或字符串存储时间间隔', 
 'CREATE TABLE tasks (duration INTERVAL DAY TO SECOND, timeout INTERVAL DAY TO SECOND);', 
 'CREATE TABLE tasks (duration_seconds INT, timeout_seconds INT);', 
 TRUE),

('OR_INTERVAL_002', 'Oracle INTERVAL YEAR TO MONTH', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bINTERVAL\\s+YEAR\\s+TO\\s+MONTH\\b', 'HIGH', 
 'GoldenDB不支持INTERVAL YEAR TO MONTH类型', 
 '使用数值类型或字符串存储时间间隔', 
 'CREATE TABLE contracts (duration INTERVAL YEAR TO MONTH, extension INTERVAL YEAR TO MONTH);', 
 'CREATE TABLE contracts (duration_months INT, extension_months INT);', 
 TRUE),

('OR_LOB_001', 'Oracle BLOB容量限制', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bBLOB\\b', 'MEDIUM', 
 'Oracle BLOB最多存储(4 gigabytes-1) * (database block size)的二进制数据；GoldenDB BLOB最多2^16-1个字节即64K', 
 '根据需要选择TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB进行替代', 
 'CREATE TABLE documents (content BLOB, attachment BLOB);', 
 'CREATE TABLE documents (content MEDIUMBLOB, attachment LONGBLOB);', 
 TRUE),

('OR_LOB_002', 'Oracle CLOB容量限制', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bCLOB\\b', 'MEDIUM', 
 'Oracle CLOB对象可以存储最多(4 gigabytes-1) * (database block size)大小的字符；GoldenDB CLOB最多存储65K', 
 '按需采用TINYTEXT（256 bytes）、TEXT（65,535 bytes ~64kb）、MEDIUMTEXT（16,777,215 bytes ~16MB）、LONGTEXT（4,294,967,295 bytes ~4GB）替换', 
 'CREATE TABLE articles (content CLOB, summary CLOB);', 
 'CREATE TABLE articles (content LONGTEXT, summary TEXT);', 
 TRUE),

('OR_LOB_003', 'Oracle RAW类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bRAW\\b', 'MEDIUM', 
 'goldendb的blob类型最大支持65K，能够兼容raw类型的2k', 
 '建议采用BLOB替换', 
 'CREATE TABLE binary_data (data RAW(2000), checksum RAW(16));', 
 'CREATE TABLE binary_data (data BLOB, checksum BLOB);', 
 TRUE),

('OR_LOB_004', 'Oracle BFILE类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bBFILE\\b', 'HIGH', 
 'GoldenDB不支持BFILE类型', 
 '使用文件路径字符串或外部存储', 
 'CREATE TABLE file_references (document BFILE, image BFILE);', 
 'CREATE TABLE file_references (document_path VARCHAR(500), image_path VARCHAR(500));', 
 TRUE),

('OR_SPECIAL_001', 'Oracle UROWID/ROWID类型', 'DATATYPE', 'ORACLE', 'GOLDENDB', 
 '\\bU?ROWID\\b', 'HIGH', 
 'GoldenDB不支持UROWID和ROWID类型', 
 '删除ROWID字段或使用其他唯一标识符', 
 'CREATE TABLE with_rowid (id NUMBER, row_id UROWID, data VARCHAR2(100));', 
 'CREATE TABLE with_rowid (id DECIMAL(10,0) PRIMARY KEY, data VARCHAR(100));', 
 TRUE),

-- 函数兼容性规则（9个）
('OR_FUNC_001', 'Oracle NVL2函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bNVL2\\s*\\(', 'MEDIUM', 
 'GoldenDB不支持NVL2函数', 
 '使用CASE WHEN表达式替换', 
 'SELECT NVL2(commission, salary + commission, salary) as total_pay FROM employees;', 
 'SELECT CASE WHEN commission IS NOT NULL THEN salary + commission ELSE salary END as total_pay FROM employees;', 
 TRUE),

('OR_FUNC_002', 'Oracle XMLAGG函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bXMLAGG\\s*\\(', 'HIGH', 
 'GoldenDB不支持XMLAGG函数', 
 '使用GROUP_CONCAT或应用层处理', 
 'SELECT XMLAGG(XMLELEMENT(NAME "employee", employee_name)) FROM employees;', 
 'SELECT GROUP_CONCAT(CONCAT(''<employee>'', employee_name, ''</employee>'')) FROM employees;', 
 TRUE),

('OR_FUNC_003', 'Oracle ROWS/RANGE窗口函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\b(ROWS|RANGE)\\s+(BETWEEN|UNBOUNDED|PRECEDING|FOLLOWING|CURRENT)', 'HIGH', 
 'GoldenDB不支持ROWS/RANGE窗口函数语法', 
 '使用其他聚合函数或子查询实现', 
 'SELECT employee_id, salary, SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as range_sum FROM employees;', 
 'SELECT employee_id, salary, (SELECT SUM(e2.salary) FROM employees e2 WHERE e2.salary BETWEEN e1.salary-1000 AND e1.salary+1000) as range_sum FROM employees e1;', 
 TRUE),

('OR_FUNC_004', 'Oracle CURRENT_TIMESTAMP不带括号', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bCURRENT_TIMESTAMP\\b(?!\\s*\\()', 'MEDIUM', 
 'Oracle的CURRENT_TIMESTAMP不需要括号，GoldenDB需要括号', 
 '添加括号：CURRENT_TIMESTAMP()', 
 'SELECT employee_id, CURRENT_TIMESTAMP as created_time FROM employees;', 
 'SELECT employee_id, CURRENT_TIMESTAMP() as created_time FROM employees;', 
 TRUE),

('OR_FUNC_005', 'Oracle SYSDATE函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bSYSDATE\\b', 'MEDIUM', 
 'GoldenDB不支持SYSDATE函数', 
 '使用NOW()函数替代', 
 'SELECT employee_id, SYSDATE as created_time FROM employees;', 
 'SELECT employee_id, NOW() as created_time FROM employees;', 
 TRUE),

('OR_FUNC_006', 'Oracle CURRENT_DATE不带括号', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bCURRENT_DATE\\b(?!\\s*\\()', 'MEDIUM', 
 'Oracle的CURRENT_DATE不需要括号，GoldenDB需要括号', 
 '添加括号：CURRENT_DATE()', 
 'SELECT employee_id, CURRENT_DATE as today FROM employees;', 
 'SELECT employee_id, CURRENT_DATE() as today FROM employees;', 
 TRUE),

('OR_FUNC_007', 'Oracle TO_NUMBER函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bTO_NUMBER\\s*\\(', 'MEDIUM', 
 'Oracle TO_NUMBER函数在GoldenDB中需要使用不同语法', 
 '使用CAST或CONVERT函数替代', 
 'SELECT TO_NUMBER(''12345'') as num_value FROM dual;', 
 'SELECT CAST(''12345'' AS DECIMAL) as num_value;', 
 TRUE),

('OR_FUNC_008', 'Oracle TO_CHAR函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bTO_CHAR\\s*\\(', 'MEDIUM', 
 'Oracle TO_CHAR函数在GoldenDB中需要使用不同语法', 
 '使用CAST、CONVERT或DATE_FORMAT函数替代', 
 'SELECT TO_CHAR(hire_date, ''YYYY-MM-DD'') as formatted_date FROM employees;', 
 'SELECT DATE_FORMAT(hire_date, ''%Y-%m-%d'') as formatted_date FROM employees;', 
 TRUE),

('OR_FUNC_009', 'Oracle TO_DATE函数', 'FUNCTION', 'ORACLE', 'GOLDENDB', 
 '\\bTO_DATE\\s*\\(', 'MEDIUM', 
 'Oracle TO_DATE函数在GoldenDB中需要使用不同语法', 
 '使用STR_TO_DATE函数替代', 
 'SELECT TO_DATE(''2023-01-15'', ''YYYY-MM-DD'') as parsed_date FROM dual;', 
 'SELECT STR_TO_DATE(''2023-01-15'', ''%Y-%m-%d'') as parsed_date;', 
 TRUE),

-- 语法兼容性规则（19个）
('OR_SYNTAX_001', 'Oracle BEGIN/END块', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bBEGIN\\s[\\s\\S]*?\\bEND\\s*;', 'HIGH', 
 'GoldenDB不支持PL/SQL的BEGIN/END块', 
 '去除begin和end关键字，将逻辑移至应用层', 
 'BEGIN UPDATE employees SET salary = salary * 1.1; INSERT INTO audit_log VALUES (SYSDATE, ''Salary updated''); END;', 
 'UPDATE employees SET salary = salary * 1.1; INSERT INTO audit_log VALUES (NOW(), ''Salary updated'');', 
 TRUE),

('OR_SYNTAX_002', 'Oracle DELETE语句省略FROM', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bDELETE\\s+(?!FROM)([a-zA-Z_][a-zA-Z0-9_]*)', 'MEDIUM', 
 'Oracle的DELETE语句可以省略FROM关键字，GoldenDB不支持', 
 '添加FROM关键字：delete from xxx', 
 'DELETE employees WHERE department_id = 10;', 
 'DELETE FROM employees WHERE department_id = 10;', 
 TRUE),

('OR_SYNTAX_003', 'Oracle注释格式', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '--(?!\\s)', 'LOW', 
 '在GoldenDB中，"--"后必须加空格', 
 '"--"后加空格', 
 'SELECT * FROM employees; --查询所有员工', 
 'SELECT * FROM employees; -- 查询所有员工', 
 TRUE),

('OR_SYNTAX_004', 'Oracle表别名区分大小写问题', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 'FROM\\s+\\w+\\s+[A-Z]+\\s+.*FROM\\s+\\w+\\s+[a-z]+', 'LOW', 
 'GoldenDB针对表别名不区分大小写，可能导致别名冲突', 
 '避免表别名冲突，使用不同的别名', 
 'SELECT A.name, a.age FROM employees A, departments a WHERE A.dept_id = a.id;', 
 'SELECT e.name, d.dept_name FROM employees e, departments d WHERE e.dept_id = d.id;', 
 TRUE),

('OR_SYNTAX_005', 'Oracle重复NULL列名', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 'SELECT\\s+.*NULL\\s*,\\s*NULL', 'MEDIUM', 
 'Oracle可行，但GoldenDB会报Duplicate column name ''NULL''', 
 '增加字段别名', 
 'SELECT NULL, NULL, employee_name FROM employees;', 
 'SELECT NULL as col1, NULL as col2, employee_name FROM employees;', 
 TRUE),

('OR_SYNTAX_006', 'Oracle双引号列名', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '"[A-Za-z_][A-Za-z0-9_]*"', 'MEDIUM', 
 'GoldenDB列名不支持加双引号', 
 '去掉双引号或是改成单引号', 
 'INSERT INTO employees ("ID", "NAME", "DEPT_ID") VALUES (1, ''John'', 10);', 
 'INSERT INTO employees (ID, NAME, DEPT_ID) VALUES (1, ''John'', 10);', 
 TRUE),

('OR_SYNTAX_007', 'Oracle SYSTEM关键字', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bSYSTEM\\b', 'MEDIUM', 
 'system在GoldenDB中是关键字，不可以作为字段名', 
 '修改字段名', 
 'CREATE TABLE config (system VARCHAR2(50), value VARCHAR2(100));', 
 'CREATE TABLE config (sys_name VARCHAR(50), value VARCHAR(100));', 
 TRUE),

('OR_SYNTAX_008', 'Oracle比较操作符空格', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '(>\\s+=|<\\s+=|!\\s+=|<\\s+>)', 'LOW', 
 'GoldenDB不支持比较操作符中间有空格（> =、< =、! =、< >中间有空格）', 
 '去除操作符之间的空格', 
 'SELECT * FROM employees WHERE salary > = 5000 AND age < = 60 AND status ! = ''INACTIVE'' AND priority < > 1;', 
 'SELECT * FROM employees WHERE salary >= 5000 AND age <= 60 AND status != ''INACTIVE'' AND priority <> 1;', 
 TRUE),

('OR_SYNTAX_009', 'Oracle DUAL表别名', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bFROM\\s+DUAL\\s+\\w+', 'MEDIUM', 
 'dual在GoldenDB中不支持有别名', 
 '删除别名', 
 'SELECT SYSDATE FROM dual d;', 
 'SELECT NOW() FROM dual;', 
 TRUE),

('OR_SYNTAX_010', 'Oracle批量插入SEQUENCE问题', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 'INSERT\\s+INTO\\s+\\w+.*SELECT.*\\w+\\.NEXTVAL', 'HIGH', 
 '批量插入/合并时使用SEQUENCE会导致SEQUENCE ID重复问题', 
 '通过编程进行改造', 
 'INSERT INTO target_table (id, name, dept_id) SELECT seq_employee.NEXTVAL, name, dept_id FROM source_table;', 
 'INSERT INTO target_table (name, dept_id) SELECT name, dept_id FROM source_table; -- 使用AUTO_INCREMENT字段', 
 TRUE),

('OR_SYNTAX_011', 'Oracle模糊查询||拼接语法', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bLIKE\\s+[^\\s]+\\s*\\|\\|\\s*[^\\s]+', 'MEDIUM', 
 '在模糊查询时，Oracle支持使用LIKE语句并使用"||"进行拼接操作，而GoldenDB不支持此方式，需要使用CONCAT函数进行拼接。', 
 '使用CONCAT函数替换||拼接操作', 
 'SELECT * FROM employees WHERE name LIKE ''%'' || search_term || ''%'';', 
 'SELECT * FROM employees WHERE name LIKE CONCAT(''%'', search_term, ''%'');', 
 TRUE),

('OR_SYNTAX_012', 'Oracle列名使用关键字冲突', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\b(key|sort)\\s+(?=\\w)', 'MEDIUM', 
 '在GoldenDB建表时，列名应避免与GoldenDB的关键字冲突，例如以"key"或者"sort"作为列名', 
 '修改列名，避免使用数据库保留关键字，建议使用带前缀或后缀的命名方式', 
 'CREATE TABLE products (id NUMBER, key VARCHAR2(50), sort NUMBER, name VARCHAR2(100));', 
 'CREATE TABLE products (id DECIMAL(10,0), product_key VARCHAR(50), sort_order DECIMAL(10,0), name VARCHAR(100));', 
 TRUE),

('OR_SYNTAX_013', 'Oracle字符编码兼容性问题', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 'CREATE\\s+TABLE\\s+.*(?!.*CHARSET\\s*=\\s*utf8mb4)', 'LOW', 
 'GoldenDB建表时建议指定为utf8mb4格式，这是utf-8的扩展编码格式，具有支持特殊字符的优势', 
 '建表时明确指定字符集为utf8mb4，确保特殊字符的正确存储和显示', 
 'CREATE TABLE users (id NUMBER, name NVARCHAR2(100), description CLOB);', 
 'CREATE TABLE users (id DECIMAL(10,0), name VARCHAR(100), description LONGTEXT) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;', 
 TRUE),

-- 新增层次查询和其他语法规则
('OR_SYNTAX_014', 'Oracle CONNECT BY层次查询', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bCONNECT\\s+BY\\b', 'HIGH', 
 'GoldenDB不支持Oracle的CONNECT BY层次查询语法', 
 '使用WITH RECURSIVE公共表表达式替代', 
 'SELECT employee_id, employee_name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id;', 
 'WITH RECURSIVE employee_hierarchy AS (SELECT employee_id, employee_name, 1 as level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id) SELECT employee_id, employee_name, level FROM employee_hierarchy;', 
 TRUE),

('OR_SYNTAX_015', 'Oracle START WITH语法', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bSTART\\s+WITH\\b', 'HIGH', 
 'GoldenDB不支持Oracle的START WITH层次查询语法', 
 '使用WITH RECURSIVE公共表表达式替代', 
 'SELECT employee_id, employee_name FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id;', 
 'WITH RECURSIVE employee_hierarchy AS (SELECT employee_id, employee_name FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.employee_name FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id) SELECT employee_id, employee_name FROM employee_hierarchy;', 
 TRUE),

('OR_SYNTAX_016', 'Oracle NULLS LAST排序', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bNULLS\\s+LAST\\b', 'MEDIUM', 
 'GoldenDB不支持NULLS LAST排序语法', 
 '使用IF(ISNULL())函数控制排序', 
 'SELECT * FROM employees ORDER BY salary DESC NULLS LAST;', 
 'SELECT * FROM employees ORDER BY IF(ISNULL(salary),0,1), salary DESC;', 
 TRUE),

('OR_SYNTAX_017', 'Oracle NULLS FIRST排序', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bNULLS\\s+FIRST\\b', 'MEDIUM', 
 'GoldenDB不支持NULLS FIRST排序语法', 
 '使用IF(ISNULL())函数控制排序', 
 'SELECT * FROM employees ORDER BY hire_date ASC NULLS FIRST;', 
 'SELECT * FROM employees ORDER BY IF(ISNULL(hire_date),1,0), hire_date ASC;', 
 TRUE),

('OR_SYNTAX_018', 'Oracle INSERT ALL语法', 'SYNTAX', 'ORACLE', 'GOLDENDB', 
 '\\bINSERT\\s+ALL\\b', 'HIGH', 
 'GoldenDB不支持Oracle的INSERT ALL批量插入语法', 
 '使用标准的INSERT INTO ... VALUES(),(),()语法', 
 'INSERT ALL INTO employees (id, name) VALUES (1, ''John'') INTO employees (id, name) VALUES (2, ''Jane'') SELECT * FROM dual;', 
 'INSERT INTO employees (id, name) VALUES (1, ''John''), (2, ''Jane'');', 
 TRUE),

-- 功能兼容性规则（4个）
('OR_FEATURE_001', 'Oracle存储过程', 'FEATURE', 'ORACLE', 'GOLDENDB', 
 '\\bCREATE\\s+(OR\\s+REPLACE\\s+)?PROCEDURE\\b', 'HIGH', 
 'Oracle存储过程在GoldenDB中不支持', 
 '通过编码实现相应功能', 
 'CREATE OR REPLACE PROCEDURE update_salary(emp_id NUMBER, new_salary NUMBER) AS BEGIN UPDATE employees SET salary = new_salary WHERE id = emp_id; END;', 
 '-- 在应用程序中实现存储过程逻辑', 
 TRUE),

('OR_FEATURE_002', 'Oracle自定义函数', 'FEATURE', 'ORACLE', 'GOLDENDB', 
 '\\bCREATE\\s+(OR\\s+REPLACE\\s+)?FUNCTION\\b', 'HIGH', 
 'Oracle自定义函数在GoldenDB中不支持', 
 '通过编码实现相应功能', 
 'CREATE OR REPLACE FUNCTION calculate_bonus(salary NUMBER) RETURN NUMBER AS BEGIN RETURN salary * 0.1; END;', 
 '-- 在应用程序中实现函数逻辑', 
 TRUE),

('OR_FEATURE_003', 'Oracle触发器', 'FEATURE', 'ORACLE', 'GOLDENDB', 
 '\\bCREATE\\s+(OR\\s+REPLACE\\s+)?TRIGGER\\b', 'HIGH', 
 'Oracle触发器在GoldenDB中不支持', 
 '通过编码实现相应功能', 
 'CREATE OR REPLACE TRIGGER trg_employee_audit AFTER INSERT OR UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log VALUES (SYSDATE, USER, :NEW.id); END;', 
 '-- 在应用程序中实现触发器逻辑', 
 TRUE),

('OR_FEATURE_004', 'Oracle SEQUENCE', 'FEATURE', 'ORACLE', 'GOLDENDB', 
 '\\bCREATE\\s+SEQUENCE\\b', 'HIGH', 
 'Oracle SEQUENCE在GoldenDB中不支持', 
 '通过分布式序列进行替换', 
 'CREATE SEQUENCE seq_employee START WITH 1 INCREMENT BY 1 MAXVALUE 999999;', 
 '-- 使用AUTO_INCREMENT字段替代 CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));', 
 TRUE);

-- ========================================
-- MySQL到GoldenDB兼容性规则  
-- ========================================

-- ================================================================
-- MySQL到GoldenDB兼容性规则集合（4个规则）
-- ================================================================
INSERT INTO compatibility_rules (rule_id, rule_name, category, source_db_type, target_db_type, rule_pattern, severity, description, suggestion, example_sql, suggested_sql, is_active) VALUES
('MY_DATE_001', 'MySQL DATE字段CURRENT_TIMESTAMP默认值', 'SYNTAX', 'MYSQL', 'GOLDENDB', 
 '\\bDATE\\s+.*\\bDEFAULT\\s+CURRENT_TIMESTAMP', 'HIGH', 
 'GoldenDB不支持DATE类型字段直接使用 CURRENT_TIMESTAMP() 作为默认值', 
 '字段可改用timestamp类型', 
 'CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, event_date DATE DEFAULT CURRENT_TIMESTAMP(), description VARCHAR(255));', 
 'CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP(), description VARCHAR(255));', 
 TRUE),

('MY_CONFIG_001', 'MySQL explicit_defaults_for_timestamp配置', 'CONFIG', 'MYSQL', 'GOLDENDB', 
 'explicit_defaults_for_timestamp\\s*=\\s*off', 'MEDIUM', 
 'MySQL默认值off，GoldenDB默认值为on。当设置为off时插入null会自动设置current timestamp值。当设置为on时则不会自动设置，因此会报错', 
 '（1）删除字段的NOT NULL约束 （2）代码适配，确保对该字段的操作值为非NULL', 
 'CREATE TABLE logs (id INT, created_at TIMESTAMP NOT NULL); INSERT INTO logs (id) VALUES (1);', 
 'CREATE TABLE logs (id INT, created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); INSERT INTO logs (id, created_at) VALUES (1, NOW());', 
 TRUE),

('MY_MERGE_001', 'MySQL MERGE INTO语法风险', 'SYNTAX', 'MYSQL', 'GOLDENDB', 
 '\\bMERGE\\s+INTO\\b', 'HIGH', 
 '在GoldenDB上使用merge into语法时，有一定概率会导致实例宕机', 
 '避免在GoldenDB上使用该语法', 
 'MERGE INTO target_table t USING source_table s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);', 
 'INSERT INTO target_table (id, name) SELECT id, name FROM source_table ON DUPLICATE KEY UPDATE name = VALUES(name);', 
 TRUE),

('MY_WITH_001', 'MySQL WITH语法风险', 'SYNTAX', 'MYSQL', 'GOLDENDB', 
 '\\bWITH\\s+\\w+\\s+AS\\s*\\(', 'HIGH', 
 '在GoldenDB上使用with语法时，有一定概率会导致实例宕机', 
 '避免在GoldenDB上使用该语法', 
 'WITH employee_summary AS (SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id) SELECT d.name, es.emp_count FROM departments d JOIN employee_summary es ON d.id = es.department_id;', 
 'SELECT d.name, sub.emp_count FROM departments d JOIN (SELECT department_id, COUNT(*) as emp_count FROM employees GROUP BY department_id) sub ON d.id = sub.department_id;', 
 TRUE);

-- ================================================================
-- 规则统计验证
-- ================================================================

-- 验证规则插入结果
SELECT 
    'Oracle→GoldenDB规则' as rule_type,
    category,
    COUNT(*) as rule_count
FROM compatibility_rules 
WHERE source_db_type = 'ORACLE' AND target_db_type = 'GOLDENDB'
GROUP BY category
UNION ALL
SELECT 
    'MySQL→GoldenDB规则' as rule_type,
    category,
    COUNT(*) as rule_count
FROM compatibility_rules 
WHERE source_db_type = 'MYSQL' AND target_db_type = 'GOLDENDB'
GROUP BY category
ORDER BY rule_type, category;

-- 统计总体规则数量
SELECT 
    source_db_type,
    target_db_type,
    COUNT(*) as total_rules,
    SUM(CASE WHEN severity = 'HIGH' THEN 1 ELSE 0 END) as high_severity,
    SUM(CASE WHEN severity = 'MEDIUM' THEN 1 ELSE 0 END) as medium_severity,
    SUM(CASE WHEN severity = 'LOW' THEN 1 ELSE 0 END) as low_severity,
    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) as active_rules
FROM compatibility_rules
GROUP BY source_db_type, target_db_type
ORDER BY source_db_type, target_db_type;

SELECT 
    'SQLCheck兼容性规则更新完成 - v4.2' as status,
    NOW() as update_time,
    '已整合生产环境规则与新增规则，总计48个兼容性规则' as description;